﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;

namespace AutoWordInfo
{
    public class MysqlServer
    {
        String connetStr = "server=127.0.0.1;port=3306;user=root;password=sasa; database=proofreadzzb;";
        public string serverIP;
        public string user;
        public string pwd;
        public string database;


        // server=127.0.0.1/localhost 代表本机，端口号port默认是3306可以不写
      

        //ini文件读写操作
        [DllImport("kernel32")]
        private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);
        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retVal, int size, string filePath);

        public MysqlServer()
        {
            LoadFromIni();
        }


        public int checkConnect()
        {
            MySqlConnection conn = new MySqlConnection(connetStr);
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
                //在这里使用代码对数据库进行增删查改
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        return 0;
                    case 1045:
                        return -1;
                    default:
                        return -2;
                }
            }
            finally
            {
                conn.Close();
            }
            return 1;
        }
        private void LoadFromIni()
        {
            string m_strINIpath = Path.GetDirectoryName(Application.ExecutablePath) + @"\setting.ini";

            StringBuilder strBdRetn = new StringBuilder();
            GetPrivateProfileString("DB", "IP", "", strBdRetn, 255, m_strINIpath);
            serverIP = strBdRetn.ToString();
            GetPrivateProfileString("DB", "Login", "", strBdRetn, 255, m_strINIpath);
            user = strBdRetn.ToString();
            GetPrivateProfileString("DB", "PWD", "", strBdRetn, 255, m_strINIpath);
            pwd = strBdRetn.ToString();

            GetPrivateProfileString("DB", "DATABASE", "", strBdRetn, 255, m_strINIpath);
            database = strBdRetn.ToString();
            connetStr = string.Format(@"server={0};port=3306;user={1};password={2};database={3};Charset=utf8;", serverIP, user, pwd, database);
        }

        public int connectMysql(string ip, string loger, string pass, string db)
        {
            connetStr = string.Format(@"server={0};port=3306;user={1};password={2};database={3};Charset=utf8", ip, loger, pass, db);
            MySqlConnection conn = new MySqlConnection(connetStr);
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
                //在这里使用代码对数据库进行增删查改
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        return 0;
                    case 1045:
                        return -1;
                    default:
                        return -2;
                }
            }
            finally
            {
                conn.Close();
            }
            return 1;
        }

        /// <summary>
        /// 通过字典表获取公告属性信息
        /// </summary>
        /// <returns></returns>
        public string getProperty()
        {
            string text = "";
            MySqlConnection conn = new MySqlConnection(connetStr);
            try
            {
                string sql = "select value from dictionary where key='PROPERTY'";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader reader = cmd.ExecuteReader();//执行ExecuteReader()返回一个MySqlDataReader对象
                while (reader.Read())//初始索引是-1，执行读取下一行数据，返回值是bool
                {
                    text = reader.GetString("value");
                    break;
                    //Console.WriteLine(reader.GetInt32("userid") + reader.GetString("username") + reader.GetString("password"));//"userid"是数据库对应的列名，推荐这种方式
                }
                reader.Close();
            }
            catch(MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        writeLog("mysql链接失败, 请检查mysql服务");
                        break;
                    case 1045:
                        writeLog("mysql链接失败, 请检查用户名和密码");
                        break;
                }
            }

            return text;
        }


        public Dictionary<long, string> getWordInfo()
        {
            Dictionary<long, string> dic = new Dictionary<long, string>();
            MySqlConnection conn = new MySqlConnection(connetStr);
            string time = DateTime.Now.ToString("yyyy-MM-dd");
            List<string> list = new List<string>();
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
                string sql = "select id,path  from announcementfile where ggStatus=0 and paperDate >=@para1";    //查询未合并的数据
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("para1", time);
                MySqlDataReader reader = cmd.ExecuteReader();//执行ExecuteReader()返回一个MySqlDataReader对象
                while (reader.Read())//初始索引是-1，执行读取下一行数据，返回值是bool
                {
                    string path = reader.GetString("path");
                    long id = reader.GetInt32("id");            //获取id   
                    list.Add(path);
                    dic.Add(id, path);
                }
                reader.Close();
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        writeLog("mysql链接失败, 请检查mysql服务");
                        break;
                    case 1045:
                        writeLog("mysql链接失败, 请检查用户名和密码");
                        break;
                }
                conn.Close();
                return dic;
            }
            MySqlTransaction transaction = conn.BeginTransaction();//事务必须在try外面赋值不然catch里的
            try
            {

                //修改状态，设置为正在合并
                string sql = "update announcementfile set ggStatus = 2 where path =@para1 and ggStatus = 0 and paperDate>=@para2";
                for (int i = 0; i < list.Count; i++)
                {
                    string path = list[i];
                    MySqlCommand cmd1 = new MySqlCommand(sql, conn);
                    cmd1.Parameters.AddWithValue("para1", path);
                    cmd1.Parameters.AddWithValue("para2", time);
                    int state = cmd1.ExecuteNonQuery();
                    if (state > 0)
                    {
                        writeLog(path+":修改状态成功");
                    }
                    else
                        writeLog(path+":修改状态失败");
                }
            }
            catch (MySqlException ex)
            {
                writeLog(ex.ToString());
                transaction.Rollback();//事务ExecuteNonQuery()执行失败报错，username被设置unique
                conn.Close();
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    transaction.Commit();//事务要么回滚要么提交，即Rollback()与Commit()只能执行一个
                    conn.Close();
                }
            }
            return dic;

        }







        public bool updateFileInfo(ActicleInfo info)
        {
            MySqlConnection conn = new MySqlConnection(connetStr);
            
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
              
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        writeLog("mysql链接失败, 请检查mysql服务");
                        break;
                    case 1045:
                        writeLog("mysql链接失败, 请检查用户名和密码");
                        break;
                }
            }

            MySqlTransaction transaction = conn.BeginTransaction();//事务必须在try外面赋值不然catch里的
            try
            {
                string sql = "update announcementfile set property =@para1, number=@para2,pageNum=@para3,wordNum=@para4,tableWordNum=@para5,tableRowNum=@para6,pictureNum=@para7,preview=@para8,ggStatus = 1 where id=@para9";
                MySqlCommand cmd1 = new MySqlCommand(sql, conn);
                cmd1.Parameters.AddWithValue("para1", info.property);
                cmd1.Parameters.AddWithValue("para2", info.paperNumber);
                cmd1.Parameters.AddWithValue("para3", info.pageNum);
                cmd1.Parameters.AddWithValue("para4", info.wordCount);
                cmd1.Parameters.AddWithValue("para5", info.tableWordNum);
                cmd1.Parameters.AddWithValue("para6", info.rowNum);
                cmd1.Parameters.AddWithValue("para7", info.pictureNum);
                cmd1.Parameters.AddWithValue("para8", info.priviewUrl);
                cmd1.Parameters.AddWithValue("para9", info.id);
                int result = cmd1.ExecuteNonQuery();
                if (result > 0)  //意味着更新成功
                {
                    writeLog(info.id.ToString() + ":更新状态成功");
                }
                else
                    writeLog(info.id.ToString() + ":更新状态失败");
            }
            catch(MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        writeLog("mysql链接失败, 请检查mysql服务");
                        break;
                    case 1045:
                        writeLog("mysql链接失败, 请检查用户名和密码");
                        break;
                }
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    transaction.Commit();//事务要么回滚要么提交，即Rollback()与Commit()只能执行一个
                    conn.Close();
                }
            }
            return true;
        }


        public bool resetState()
        {
            MySqlConnection conn = new MySqlConnection(connetStr);
            string time = DateTime.Now.ToString("yyyy-MM-dd");
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        writeLog("mysql链接失败, 请检查mysql服务");
                        break;
                    case 1045:
                        writeLog("mysql链接失败, 请检查用户名和密码");
                        break;
                }
            }
            MySqlTransaction transaction = conn.BeginTransaction();//事务必须在try外面赋值不然catch里的
            try
            {
                //修改状态，设置为正在合并
               
                string sql = "update announcementfile set ggStatus = 0 where  ggStatus = 2 and paperDate>=@para1";
                MySqlCommand cmd1 = new MySqlCommand(sql, conn);
                cmd1.Parameters.AddWithValue("para1", time);
                int result = cmd1.ExecuteNonQuery();
                if (result > 0)  //意味着更新成功
                {
                    writeLog(time + "---更新至待读取状态成功");
                }
                else
                    writeLog(time + ":更新至待读取状态失败");
            }
            catch(MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        writeLog("mysql链接失败, 请检查mysql服务");
                        break;
                    case 1045:
                        writeLog("mysql链接失败, 请检查用户名和密码");
                        break;
                }
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    transaction.Commit();//事务要么回滚要么提交，即Rollback()与Commit()只能执行一个
                    conn.Close();
                }
            }
            return true;
        }


        /************************************************************************/
        /*function: 错误日志文件,将错误日志记录下来
         *fileName：文件名
         *message： 错误日志信息
        /************************************************************************/
        public void writeLog(string message)
        {
            string workPath = System.AppDomain.CurrentDomain.BaseDirectory;  //获取工作路径
            string logPath = workPath + "log";
            string time = DateTime.Now.ToString("yyyy-MM-dd");
            string logFile = logPath + "\\mysql-" + time + ".log";
            string msg = "";
            try
            {
                if (!Directory.Exists(logPath))
                {
                    Directory.CreateDirectory(logPath);
                }
                FileStream fs;
                StreamWriter sw;
                if (File.Exists(logFile))  //验证文件是否存在，有则追加，无则创建
                {
                    fs = new FileStream(logFile, FileMode.Append, FileAccess.Write);
                }
                else
                {
                    fs = new FileStream(logFile, FileMode.Create, FileAccess.Write);
                }
                sw = new StreamWriter(fs);
                msg = message + "-----" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
                sw.WriteLine(msg);
                sw.Close();
                fs.Close();
            }
            catch (System.Exception ex)
            {
                return;
            }


        }


    }
}
